clear all 
set more off

global mydir "C:\XYZ" // set working directory

cd "$mydir\data\demographics"
set matsize 11000
set maxvar 32700

import excel raw\block2010_CA_pop_house.xlsx, firstrow clear
rename BLOCKID10 geoid10
sort geoid10
tempfile tmp
save `tmp', replace

import delimited raw\block2010_CA_area.csv, clear
sort geoid10
drop if aland10==0
merge 1:1 geoid10 using `tmp'
erase `tmp'
keep if _merge==3
drop _merge
rename countyfp10 cntygeo
sort cntygeo
tempfile tmp
save `tmp', replace
import excel raw\CountyName_Code.xlsx, firstrow clear
merge 1:m cntygeo using `tmp'
drop _merge
gen cnty=county
replace cnty="Yuba-Sutter" if county=="Yuba"
replace cnty="Yuba-Sutter" if county=="Sutter"
sort cnty
by cnty: egen cpop=total(POP10)
gen wlat=POP10*intptlat10
gen wlon=POP10*intptlon10
by cnty: egen ctlat=total(wlat)
by cnty: egen ctlon=total(wlon)
gen cpwlat=round(ctlat/cpop,.000001)
gen cpwlon=round(ctlon/cpop,.000001)
by cnty: gen dup=cond(_N==1,0,_n)
keep if dup<=1
keep cnty cpwlat cpwlon
rename cnty county
save county_population_weighted_coordinate, replace


